package br.ufc.storm.Control;

import java.io.IOException;
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.util.ArrayList; 
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBElement;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Marshaller;
import javax.xml.transform.Result;

import com.sun.media.jfxmedia.events.NewFrameEvent;

import Exception.StormException;
import br.ufc.storm.jaxb.AbstractComponentType;
import br.ufc.storm.jaxb.ContextArgumentType;
import br.ufc.storm.jaxb.ContextContract;
import br.ufc.storm.jaxb.ContextParameterType;
import br.ufc.storm.jaxb.ContextVariableType;
import br.ufc.storm.jaxb.InstantiationTypeType;
import br.ufc.storm.jaxb.ObjectFactory;


/**
 * 
 * @author wagner
 *
 */
public class DBHandler {
	private final static String INSERT_ABSTRACT_COMPONENT ="INSERT INTO abstract_component (ac_name, supertype_id, kind_id, parent) VALUES (?, ?, ?, ?);" ;
	private final static String INSERT_CONTEXT_PARAMETER ="INSERT INTO context_parameter (bound_id, cp_name, ac_id,context_variable_name) VALUES ((select cc_id from context_contract where cc_name=?), ? ,(select ac_id from abstract_component where ac_name=?), ?);";
	private static final String INSERT_CONTEXT_ARGUMENT = "INSERT INTO context_argument (cc_id, cp_id) VALUES ((select cc_id from context_contract where cc_name=?),(select cp_id from context_parameter where cp_name=?));";
	//	private static final String INSERT_OPEN_CONTEXT_ARGUMENT = " INSERT INTO open_argument  (argument_id) VALUES (?);";
	private static final String INSERT_CC_CLOSED_CONTEXT_ARGUMENT = " INSERT INTO closed_arguments_context_contract  (ca_id, cc_id) VALUES (?,?);";
	private static final String INSERT_VALUE_CLOSED_CONTEXT_ARGUMENT = " INSERT INTO closed_argument_values  (ca_id, value) VALUES (?,?);";
	private final static String INSERT_CONTEXT_CONTRACT = "INSERT INTO context_contract (ac_id, cc_name) VALUES ((select ac_id from abstract_component where ac_name = ?), ?);";
	private final static String INSERT_INNER_COMPONENT = "INSERT INTO inner_components (parent_id, inner_component_name, component_id) VALUES ((select ac_id from abstract_component where ac_name = ?), ?, ?);";
	private final static String SELECT_CONTEXT_ARGUMENT = "select * from context_argument where cc_id = (SELECT cc_id from context_contract where cc_name = ?);";
	private final static String SELECT_CONTEXT_ARGUMENT_BY_ID = "select * from context_argument where cc_id = ?;";
	private final static String SELECT_COMPONENT_ID = "select ac_id from abstract_component where ac_name=?;";
	private final static String SELECT_COMPONENT_KIND = "select kind_id from abstract_component where ac_id=?;";
	private final static String SELECT_INNER_COMPONENT_ID = "select inner_component_id from inner_components where inner_component_name=?;";
	private final static String SELECT_KIND_ID = "select kind_id from component_kind where kind_name=?;";
	private final static String SELECT_KIND_NAME = "select kind_name from component_kind where kind_id=?;";
	private final static String SELECT_ABSTRACT_COMPONENT = "select ac_name, ac_id, supertype_id, parent, kind_id from abstract_component WHERE ac_name = ?;";
	private final static String SELECT_ABSTRACT_COMPONENT_BY_ID = "select ac_name, ac_id, supertype_id, parent, kind_id from abstract_component WHERE ac_id = ?;";
	private final static String SELECT_COMPONENT_PARAMETER = "select * from context_parameter where ac_id = ?;";
	private final static String SELECT_ALL_ABSTRACTCOMPONENT = "select ac_name,ac_id,supertype_id,kind_id,parent from abstract_component;";
	private final static String SELECT_COMPONENT_NAME = "select ac_name from abstract_component where ac_id=?;";
	private final static String SELECT_CONTEXT_CONTRACT_ID = "select cc_id from context_contract where cc_name = ?;";
	private final static String SELECT_CONTEXT_PARAMETER_ID = " select cp_id from context_parameter where cp_name = ?;";
	private static final String SELECT_CONTEXT_CONTRACT_NAME = "select cc_name from context_contract where cc_id = ?;";
	private final static String UPDATE_ABSTRACT_COMPONENT = "update abstract_component set enabled=false where ac_name = ?;";
	private static final String INSERT_CONCRETE_UNIT = "INSERT INTO concrete_unit ( cc_id, au_id) VALUES (?, ?);";
	private static final String SELECT_CONCRETE_UNIT_ID = "select unit_id from concrete_unit where au_id = (SELECT abstract_unit_id from abstract_unit where au_name = ?);";
	private static final String INSERT_ABSTRACT_UNIT = "INSERT INTO abstract_unit ( ac_id, au_name) VALUES ((select ac_id from abstract_component where ac_name = ?), ?);";
	private static final String SELECT_ABTRACT_UNIT_ID = "select abstract_unit_id from abstract_unit where au_name = ?;";
	private static final String INSERT_UNIT_FILE = "INSERT INTO files (file_name, u_id, file_extension, is_folder, current_version, build_cfg, file_type) VALUES (?, ?, ?, ?, ?, ?,(select type_id from file_type where type_name = ?));";
	private static final String GENERATE_ABSTRACT_COMPONENT_TREE = "with recursive abstract_components (ac_id, supertype_id, ac_name) as ( select ac_id, supertype_id, ac_name from abstract_component where ac_id = ? UNION ALL select abstract_component.ac_id,abstract_component.supertype_id, abstract_component.ac_name from abstract_component INNER JOIN abstract_components ON abstract_component.supertype_id = abstract_components.ac_id WHERE abstract_components.ac_id <> ?) select context_contract.cc_id, context_contract.cc_name FROM context_contract INNER JOIN abstract_components ON context_contract.ac_id = abstract_components.ac_id;";// First argument is the limit and the second is the specification
	private static final String GENERATE_SUBTYPE_TREE_COVARIANT = "with recursive abstract_components (ac_id, supertype_id, ac_name) as ( select ac_id, supertype_id, ac_name from abstract_component where ac_id = ? UNION ALL select abstract_component.ac_id,abstract_component.supertype_id, abstract_component.ac_name from abstract_component INNER JOIN abstract_components ON abstract_component.supertype_id = abstract_components.ac_id) select context_contract.cc_id, context_contract.cc_name FROM context_contract INNER JOIN abstract_components ON context_contract.ac_id = abstract_components.ac_id;";
	private static final String SELECT_SUPERTYPE = "select supertype_id from abstract_component  WHERE ac_id = ?;";
	private static final String SELECT_CONTEXT_CONTRACT_BY_ID = "select cc_id from context_contract where ac_id = ?;";
	private static final String SELECT_CONTEXT_CONTRACT = "select cc_id, ac_id, cc_name from context_contract where cc_id = ?;";
	private static final String SELECT_CONTEXT_ARGUMENT_VALUE = "SELECT ca_value FROM context_argument  WHERE cp_id = ? AND cc_id = ?;";
	private static final String DELETE_OPEN_CONTEXT_ARGUMENT = "DELETE FROM open_argument WHERE argument_id  = ?;";

	/**
	 * This method adds an Abstract Component into components library
	 * @param ac object
	 * @return Abstract Component added id
	 */
	public static int addAbstractComponent(AbstractComponentType ac){
		Connection con = null; 
		String ac_name = ac.getName();
		try { 
			con = getConnection(); 

			PreparedStatement prepared = con.prepareStatement(INSERT_ABSTRACT_COMPONENT); 
			prepared.setString(1, ac.getName()); 
			prepared.setInt(2, ac.getSupertype().getIdAc());
			prepared.setInt(3, DBHandler.getKindID(ac.getKind()));
			prepared.setInt(4, ac.getParent().getIdAc());
			prepared.execute();

		} catch (SQLException e) { e.printStackTrace(); 
		} finally { closeConnnection(con); 
		} 

		return DBHandler.getAbstractComponentID(ac_name);
	}

	/**
	 * This method get an abstract component from components library
	 * @param compName
	 * @return Abstract component if found, else it returns null
	 */
	public static AbstractComponentType getAbstractComponent(String compName){
		Connection con = null; 
		Result str = null;
		int ac_id = 0, supertype_id = 0, kind_id = 0, parent = 0;
		String name = null;
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_ABSTRACT_COMPONENT); 
			prepared.setString(1, compName);
			ResultSet resultSet = prepared.executeQuery(); 
			while (resultSet.next()) { 
				ac_id = resultSet.getInt("ac_id"); 
				name=resultSet.getString("ac_name");
				supertype_id = resultSet.getInt("supertype_id"); 
				parent = resultSet.getInt("parent");
				kind_id = resultSet.getInt("kind_id"); 
			} 
			AbstractComponentType ac = new AbstractComponentType();
			ac.setIdAc(ac_id);
			ac.setName(name);
			ac.setSupertype(new AbstractComponentType());
			ac.getSupertype().setIdAc(supertype_id);
			ac.setParent(new AbstractComponentType());
			ac.getParent().setIdAc(parent);
			ac.setKind(kind_id+"");
			List<ContextParameterType> t = DBHandler.getContextParameter(ac_id);
			ac.getContextParameter().addAll(t);
			return ac; 
		} catch (SQLException e) { 
			e.printStackTrace(); 
		} finally { closeConnnection(con); 
		} 
		return null;
	}


	/**
	 * This method get an abstract component from components library
	 * @param compName
	 * @return Abstract component if found, else it returns null
	 */
	public static AbstractComponentType getAbstractComponent(int ac_id){
		Connection con = null; 
		Result str = null;
		int supertype_id = 0, kind_id = 0, parent = 0;
		String name = null;
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_ABSTRACT_COMPONENT_BY_ID); 
			prepared.setInt(1, ac_id);
			ResultSet resultSet = prepared.executeQuery(); 
			while (resultSet.next()) { 
				name=resultSet.getString("ac_name");
				supertype_id = resultSet.getInt("supertype_id"); 
				parent = resultSet.getInt("parent");
				kind_id = resultSet.getInt("kind_id"); 
			} 
			AbstractComponentType ac = new AbstractComponentType();
			ac.setIdAc(ac_id);
			ac.setName(name);
			ac.setSupertype(new AbstractComponentType());
			ac.getSupertype().setIdAc(supertype_id);
			ac.setParent(new AbstractComponentType());
			ac.getParent().setIdAc(parent);
			ac.setKind(kind_id+"");
			List<ContextParameterType> t = DBHandler.getContextParameter(ac_id);
			ac.getContextParameter().addAll(t);
			return ac; 
		} catch (SQLException e) { 
			e.printStackTrace(); 
		} finally { closeConnnection(con); 
		} 
		return null;
	}

	/**
	 * This method returns an abstract component name given an id
	 * @param id
	 * @return abstract component name
	 */

	public static String getAbstractComponentName(int id){
		Connection con = null; 
		String ac_name = null;
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_COMPONENT_NAME); 
			prepared.setInt(1, id); 
			ResultSet resultSet = prepared.executeQuery(); 
			if(resultSet.next()) { 
				ac_name = resultSet.getString("ac_name"); 
			}
		} catch (SQLException e) { 
			e.printStackTrace(); 
		} finally { closeConnnection(con); 
		} 
		return ac_name;
	}

	/**
	 * This method returns a boolean value true if component found is is sub type from required one
	 * @param id
	 * @return abstract component name
	 */

	//Covariant Relation: conversão de um tipo mais específico para um tipo mais genérico.
	//Contravariant Relation: conversão se dá do mais genérico para o mais específico.
	public static boolean isSubType(ContextContract applicationContract, ContextContract candidate){
		boolean subtype = false;
		int kind = DBHandler.getComponentKind(applicationContract.getAbstractComponent().getIdAc());
		switch(kind){
		case 4:

			//Get argument value and test if is equal or bigger than specified
			/*if(DBHandler.getContextArgumentValue(candidate.getCcId(), cp_id)>DBHandler.getContextArgumentValue(cc_id, cp_id)){

			}else{

			}*/

			break;
		case 5: 
			//			TODO:Complete this method

			break;
		default:

			int supertypeID = applicationContract.getAbstractComponent().getSupertype().getIdAc();

			Connection con = null; 
			int supertype = 0;
			int aux = candidate.getCcId();
			try { 
				con = getConnection(); 
				do{
					PreparedStatement prepared = con.prepareStatement(SELECT_SUPERTYPE); 
					prepared.setInt(1, aux); 
					ResultSet resultSet = prepared.executeQuery(); 
					if(resultSet.next()) { 
						supertype = resultSet.getInt("supertype_id"); 
					}
					if(supertype == supertypeID){
						subtype = true;
					}
					aux = supertype;
				}while(supertype != supertypeID && supertype != 0);
			} catch (SQLException e) { 
				//e.printStackTrace(); 
			} finally { closeConnnection(con); 
			} 

			if(applicationContract.getContextArguments().size() > 0){
				List<ContextArgumentType> cats  = applicationContract.getContextArguments();
				for(ContextArgumentType cat: cats){
					int sup = cat.getInstantiationType().getAbstractComponent().getSupertype().getIdAc();
					//					TODO: Verificar o que falta aqui e completar
					//					subtype = subtype && isSubType(cat.getInstantiationType(), sup);
				}
			}

		}
		return subtype;
	}

	/*Este método irá testar se um componente abstrato referente a um argumento de contexto 
	da aplicação é subtipo do limite do componente referente ao candidato*/
	public static boolean testContextParameter(int abstractComponentBoundID, int candidateID){
//		TODO: Fazer este método

		return false;
	}

	/**
	 * This method generates a list os all components candidates to a contract but only in the highest level, it will be refined.
	 * @param supertypeID
	 * @param requiredID
	 * @return
	 */

	public static List<ContextContract> generateCandidates(int supertypeID, int requiredID){
		PreparedStatement prepared;
		ResultSet resultSet;
		List<ContextContract> list = new ArrayList<ContextContract>();
		Connection con = null; 
		int history;
		int supertype = 0;
		int aux = requiredID;
		try { 
			con = getConnection();
			do{
				prepared = con.prepareStatement(SELECT_CONTEXT_CONTRACT_BY_ID); 
				prepared.setInt(1, aux); 
				resultSet = prepared.executeQuery(); 
				while(resultSet.next()) { 
					Integer cc_id = resultSet.getInt("cc_id");
					list.add(DBHandler.getContextContract(cc_id)); 
				}
				prepared = con.prepareStatement(SELECT_SUPERTYPE); 
				prepared.setInt(1, aux); 
				resultSet = prepared.executeQuery(); 
				if(resultSet.next()) { 
					supertype = resultSet.getInt("supertype_id"); 
				}
				history = aux;
				aux = supertype;
			}while(resultSet!= null && history != supertypeID );//supertype != supertypeID && supertype != 0
		} catch (SQLException e) { 
			e.printStackTrace(); 
		} finally { closeConnnection(con); 
		} 
		return list;
	}


	/**
	 * This method generates and returns a list of all components compatibles with contract
	 * @param id
	 * @return abstract component name
	 */

	public static List<ContextContract> resolve(ContextContract cc){
		List<ContextContract> candidates = DBHandler.generateCandidates(cc.getAbstractComponent().getSupertype().getIdAc(), cc.getAbstractComponent().getIdAc());
		List<ContextContract> act = new ArrayList<ContextContract>();
		for(ContextContract candidate:candidates){
			if(DBHandler.isSubType(cc, candidate)){
				act.add(DBHandler.getContextContract(candidate.getCcId()));	
			}
		}

		return act;
	}

	/**
	 * This method returns a context contract
	 * @param id his id
	 * @return
	 */

	public static ContextContract getContextContract(Integer id) {
		Connection con = null; 
		Result str = null;
		int ac_id = 0;
		int cc_id = 0;
		String name = null;
		ContextContract cc = null;
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_CONTEXT_CONTRACT); 
			prepared.setInt(1, id);
			ResultSet resultSet = prepared.executeQuery(); 
			while (resultSet.next()){
				cc_id = resultSet.getInt("cc_id"); 
				ac_id = resultSet.getInt("ac_id"); 
				name=resultSet.getString("cc_name"); 
			} 
			cc = new ContextContract();
			cc.setCcId(cc_id);
			cc.setCcName(name);
			cc.setAbstractComponent(DBHandler.getAbstractComponent(ac_id));
			//			TODO:Populating with ContextArguments

			List<ContextArgumentType> cat = new ArrayList<ContextArgumentType>();
			Connection con2 = getConnection(); 
			PreparedStatement prepared2 = con2.prepareStatement(SELECT_CONTEXT_ARGUMENT_BY_ID); 
			prepared2.setInt(1, cc_id);
			ResultSet resultSet2 = prepared2.executeQuery(); 
			while (resultSet2.next()){
				int cc2_id = resultSet2.getInt("cc_id"); 
				int cp_id = resultSet2.getInt("cp_id"); 
				int ca_id = resultSet2.getInt("ca_id");
			} 


			for(ContextParameterType cp : cc.getAbstractComponent().getContextParameter()){

			}

			//End of context arguments populate
		} catch (SQLException e) { 
			e.printStackTrace(); 
		} finally { closeConnnection(con); 
		} 
		return cc;
	}

	/*public static boolean resolveWalk(AbstractComponentType cc, String bound){

	 *//**
	 * Este método deve receber uma variável de contexto e validar se ela é subtipo do contrato 
	 * contextual informado, e recursivamente ela irá passar pelos parâmetros de contexto, 
	 * os quais a chamarão novamente.
	 *//*
		boolean r = true;
		if(isSubType(DBHandler.getAbstractComponentID(bound), cc.getIdAc())){
			List<ContextParameterType> cps = cc.getContextParameter();
			for(ContextParameterType p:cps){
				if(!isSubType(p.getBound().getIdAc(), p.getAbstractComponent().getIdAc())){
					r = false;
				}
			}
		}
		return true;
	}*/
	/*
	public static boolean resolveWalk(InstantiationType cc, String bound){
		boolean r = true;
		if(isSubType(DBHandler.getAbstractComponentID(bound), cc.getAc().getID())){
			ArrayList<ContextParameter> cps = cc.getAc().getcParam();
			for(ContextParameter p:cps){
				if(!isSubType(p.getBound(), ((ContextVariable) p.getContextVariable().getValue()).getAc().getID())){
					r = false;
				}
			}
		}
		return true;
	}

	 */














	/**
	 * This method returns an abstract component id given an name
	 * @param name
	 * @return abstract component id
	 */

	public static int getAbstractComponentID(String name){
		Connection con = null; 
		int ac_id = -2;
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_COMPONENT_ID); 
			prepared.setString(1, name); 
			ResultSet resultSet = prepared.executeQuery(); 
			if(resultSet.next()) { 
				ac_id = resultSet.getInt("ac_id"); 
			}
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return -1;
		} finally { 
			closeConnnection(con); 
		} 
		return ac_id;
	}

	/**
	 * This method is responsible for set a component as removed, disabling it in the database
	 * @param name Abstract component name
	 * @return true if correctly set as removed
	 */
	public static boolean setObsolete(String name){
		Connection con = null; 
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(UPDATE_ABSTRACT_COMPONENT); 
			prepared.setString(1, name); 
			prepared.execute();
			return true;
		} catch (SQLException e) { e.printStackTrace(); 
		} finally { closeConnnection(con); 
		} 
		return false;


	}

	/**
	 * This method adds a context argument
	 * @param cc_name Instantiation type name 
	 * @param cp_name Context parameter name
	 * @param ca_value Context argument value
	 * @return True if well successful
	 */

	public static boolean addContextArgument(String cc_name, String cp_name, String ca_value){
		Connection con = null; 
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(INSERT_CONTEXT_ARGUMENT); 
			prepared.setString(1, cc_name); 
			prepared.setString(2, cp_name); 
			prepared.setString(3, ca_value); 
			prepared.executeQuery(); 
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return false;
		} finally { closeConnnection(con); 
		} 
		return true;
	}

	/*public static boolean removeOpenContextArgument(int id){
		Connection con = null; 
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(DELETE_OPEN_CONTEXT_ARGUMENT); 
			prepared.setInt(1, id); 
			prepared.execute(); 
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return false;
		} finally { closeConnnection(con); 
		} 
		return true;
	}

	public static boolean closeContextArgument(int cp_id, int cc_id){


		return false;
	}*/

	/**
	 * This method gets all context arguments given an instantiation type name
	 * @param cc_name Instantiation type name
	 * @return Array of context arguments
	 */
	public static ArrayList<ContextArgumentType> getContextArgument(String cc_name) {
		Connection con = null; 
		ArrayList<ContextArgumentType> cpl = new ArrayList<ContextArgumentType>();
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_CONTEXT_ARGUMENT); 
			prepared.setString(1, cc_name);
			ResultSet resultSet = prepared.executeQuery(); 
			while (resultSet.next()) {
				ContextArgumentType ca = new ContextArgumentType();
				ca.setItId(resultSet.getInt("cc_id"));
				ca.setCpId(resultSet.getInt("cp_id")); 
				AbstractComponentType a = new AbstractComponentType();
				a.setIdAc(resultSet.getInt("ca_id"));
				ca.setAc(a);
				ca.setValue(resultSet.getString("ca_value"));
				cpl.add(ca);
			} 
		} catch (SQLException e) { 
			e.printStackTrace(); 
		} finally { closeConnnection(con); 
		} 
		return cpl; 
	}

	/**
	 * This method should test if a component do not generate infinite loops in composition walk
	 * 
	 * @param acid
	 * @param bound
	 * @return
	 */

	public static boolean validateContexParameter(int acid, int bound){
		//TODO: Generated to evaluate the context parameters 


		return true;
	}

	/**
	 * This method adds a context parameter
	 * @param name Context parameter
	 * @param bound_name Bound name
	 * @param abstractcomponent_name Abstract component name
	 * @param context_variable_name Context variable name
	 * @return Context parameter id from added context parameter
	 */

	public static int addContextParameter(String name, String bound_name, String abstractcomponent_name, String context_variable_name){
		Connection con = null; 
		try { 
			if(DBHandler.validateContexParameter(DBHandler.getAbstractComponentID(abstractcomponent_name), DBHandler.getAbstractComponentID(bound_name))==false){
				throw new StormException("Composition tree violated");
			}
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(INSERT_CONTEXT_PARAMETER); 
			prepared.setString(1, bound_name); 
			prepared.setString(2, name); 
			prepared.setString(3, abstractcomponent_name); 
			prepared.setString(4, context_variable_name); 
			prepared.executeQuery(); 
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return -1;
		} catch (StormException e) {
			return -1;
		} finally { closeConnnection(con); 
		} 
		return getContextParameterID(name);
	}

	/**
	 * This method gets a context parameters from a given abstract component id
	 * @param id Abstract component id
	 * @return Array of context parameters
	 */

	public static List<ContextParameterType> getContextParameter(int id) {
		/*		
		Validar se não vai fechar um ciclo entre limites e componentes
		Criar uma tabela para validar esta possibilidade, se fechar ciclo, disparar uma exception.
		 */
		Connection con = null; 
		List<ContextParameterType> cpl = new ArrayList<ContextParameterType>();
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_COMPONENT_PARAMETER); 
			prepared.setInt(1, id);
			ResultSet resultSet = prepared.executeQuery(); 
			while (resultSet.next()) {
				ContextParameterType cp = new ContextParameterType();
				cp.setName(resultSet.getString("cp_name"));
				int bound_id = resultSet.getInt("bound_id");
				AbstractComponentType ac;
				try{
					if(bound_id != id){
						ac = DBHandler.getAbstractComponent(bound_id);  
					}else{
						throw new StormException("Context Parameter bound self referenced results in infinite loop");
					}
				}catch (StormException e) {
					ac = new AbstractComponentType();
					ac.setIdAc(bound_id);
				}
				cp.setBound(ac); 
				cp.setContextVariable(null); 
				cpl.add(cp);
			} 

		} catch (SQLException e) { 
			e.printStackTrace(); 
		} finally { closeConnnection(con); 
		} 
		return cpl; 
	}

	/**
	 * This method gets a context parameter id given a context parameter name
	 * @param name Context parameter name
	 * @return context parameter id
	 */
	private static int getContextParameterID(String name) {
		Connection con = null; 
		int CP_id = -2;
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_CONTEXT_PARAMETER_ID); 
			prepared.setString(1, name); 
			ResultSet resultSet = prepared.executeQuery(); 
			if(resultSet.next()) { 
				CP_id = resultSet.getInt("cp_id"); 
			}
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return -1;
		} finally { 
			closeConnnection(con); 
		} 
		return CP_id;
	}

	/**
	 * This method adds an instantiation type 
	 * @param name Instantiation type name
	 * @param ac_name Abstract component name 
	 * @return instantiation type id
	 */

	public static int addInstantiationType(String name, String ac_name){
		Connection con = null; 
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(INSERT_CONTEXT_CONTRACT); 
			prepared.setString(1, ac_name); 
			prepared.setString(2,  name); 
			prepared.executeQuery(); 	
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return -1;
		} finally { closeConnnection(con); 
		} 
		return getInstantiationTypeID(name);
	}

	/**
	 * This method gets an instantiation type name given its bound
	 * @param cc_id Instantiation type id
	 * @return Instantiation type name
	 */
	public static String getInstantiationTypeName(int cc_id) {
		Connection con = null; 
		String name = null;
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_CONTEXT_CONTRACT_NAME); 
			prepared.setInt(1, cc_id); 
			ResultSet resultSet = prepared.executeQuery(); 
			if(resultSet.next()) { 
				name = resultSet.getString("cc_name"); 
			}
		} catch (SQLException e) { 
			e.printStackTrace(); 
		} finally { closeConnnection(con); 
		} 
		return name;
	}

	/**
	 * This method gets an instantiation type id
	 * @param name Instantiation type name
	 * @return Instantiation type id
	 */
	public static int getInstantiationTypeID(String name){
		Connection con = null; 
		int cc_id = -1;
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_CONTEXT_CONTRACT_ID); 
			prepared.setString(1, name); 
			ResultSet resultSet = prepared.executeQuery(); 
			if(resultSet.next()) { 
				cc_id = resultSet.getInt("cc_id"); 
			}
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return -1;
		} finally { closeConnnection(con); 
		} 
		return cc_id;
	}

	/**
	 * This method gets an instantiation type id
	 * @param name Instantiation type name
	 * @return Instantiation type id
	 */
	public static List<Integer> getInstantiationTypeByAcId(int id){
		Connection con = null;
		List<Integer> list = new ArrayList<Integer>();
		int cc_id = -1;
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_CONTEXT_CONTRACT_BY_ID); 
			prepared.setInt(1, id); 
			ResultSet resultSet = prepared.executeQuery(); 
			if(resultSet.next()) { 
				cc_id = resultSet.getInt("cc_id"); 
				list.add(cc_id);
			}
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return null;
		} finally { closeConnnection(con); 
		} 
		return list;
	}


	/**
	 * This method adds an inner component 
	 * @param name Inner component name 
	 * @param parent_name Parent abstract component name
	 * @return True if well successfully
	 */

	public static boolean addInnerComponent(String ic_name, String name, String parent_name) {
		Connection con = null; 
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(INSERT_INNER_COMPONENT); 
			prepared.setString(1, parent_name); 
			prepared.setString(2, name);  
			prepared.setString(3, ic_name);
			prepared.executeQuery(); 
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return false;
		} finally { 
			closeConnnection(con); 
		} 
		return true;
	}

	/**
	 * This method gets an inner component id
	 * @param name Inner component name
	 * @return Inner component id
	 */
	public static int getInnerComponentID(String name) {
		Connection con = null; 
		int ac_id = -2;
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_INNER_COMPONENT_ID); 
			prepared.setString(1, name); 
			ResultSet resultSet = prepared.executeQuery(); 
			if(resultSet.next()) { 
				ac_id = resultSet.getInt("inner_component_id"); 
			}
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return -1;
		} finally { 
			closeConnnection(con); 
		} 
		return ac_id;
	}

	/**
	 * This method gets a kind id
	 * @param kind Kind name
	 * @return Kind id
	 */

	public static int getKindID(String kind) {
		Connection con = null; 
		int kind_id = -2;
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_KIND_ID); 
			prepared.setString(1, kind); 
			ResultSet resultSet = prepared.executeQuery(); 
			if(resultSet.next()) { 
				kind_id = resultSet.getInt("kind_id"); 
			}
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return -1;
		} finally { 
			closeConnnection(con); 
		} 
		return kind_id;
	}

	/**
	 * This method gets a kind name
	 * @param id Kind id
	 * @return Kind name
	 */
	public static String getKindName(int id) {
		Connection con = null; 
		String name = null;
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_KIND_NAME); 
			prepared.setInt(1, id); 
			ResultSet resultSet = prepared.executeQuery(); 
			if(resultSet.next()) { 
				name = resultSet.getString("kind_name"); 
			}
		} catch (SQLException e) { 
			e.printStackTrace(); 
		} finally { 
			closeConnnection(con); 
		} 
		return name;
	}
	/**
	 * 
	 * @param au_name
	 * @param cc_name
	 * @return
	 */
	public static int addAbstractUnit(String au_name, String cc_name){
		Connection con = null; 
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(INSERT_ABSTRACT_UNIT); 
			prepared.setString(1, cc_name); 
			prepared.setString(2, au_name);  
			prepared.executeUpdate(); 
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return -1;
		} finally { 
			closeConnnection(con); 
		} 
		return DBHandler.getAbstractUnitID(au_name);
	}

	public static int getAbstractUnitID(String au_name){
		Connection con = null; 
		int au_id = -2;
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_ABTRACT_UNIT_ID); 
			prepared.setString(1, au_name); 
			ResultSet resultSet = prepared.executeQuery(); 
			if(resultSet.next()) { 
				au_id = resultSet.getInt("abstract_unit_id"); 
			}
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return -1;
		} finally { 
			closeConnnection(con); 
		} 
		return au_id;
	}

	/**
	 * This method adds an unit 
	 * @param ccId 
	 * @param parent_name 
	 * @return True if well successfully
	 */

	public static boolean addConcreteUnit(String concrete_componentID, String au_id) {
		Connection con = null; 
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(INSERT_CONCRETE_UNIT); 
			prepared.setInt(1, Integer.parseInt(concrete_componentID)); 
			prepared.setInt(2, Integer.parseInt(au_id)); 
			//System.out.println(">>>>>>>>>>>>>>>>>>> "+prepared);
			prepared.executeUpdate(); 
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return false;
		} finally { closeConnnection(con); 
		} 
		return true;
	}

	/**
	 * This method adds a file to an concrete unit
	 * @param name 
	 * @param parent_name 
	 * @return True if well successfully
	 */

	public static boolean addConcreteUnitFile(String filename, String uid, String extension, String folder, String build_cfg, String version, String filetype) {
		Connection con = null; 
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(INSERT_UNIT_FILE); 
			prepared.setString(1, filename); 
			prepared.setInt(2, Integer.parseInt(uid));  
			prepared.setString(3, extension); 
			prepared.setBoolean(4, Boolean.parseBoolean(folder));
			prepared.setInt(5, Integer.parseInt(version)); 
			prepared.setString(6, build_cfg);
			prepared.setString(7, filetype);
			prepared.executeUpdate(); 
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return false;
		} finally { closeConnnection(con); 
		} 
		return true;
	}

	/**
	 * This method returns a concrete unit id
	 * @param name
	 * @return
	 */

	public static int getConcreteUnitID(String name){
		Connection con = null; 
		int c_unit_id = -2;
		try {
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_CONCRETE_UNIT_ID); 
			prepared.setString(1, name); 
			ResultSet resultSet = prepared.executeQuery(); 
			if(resultSet.next()) { 
				c_unit_id = resultSet.getInt("unit_id"); 
			}
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return -1;
		} finally { 
			closeConnnection(con); 
		} 
		return c_unit_id;
	}







	/**
	 * This method gets the list of all abstract components in the library
	 * @return List of components
	 */

	public static ArrayList<AbstractComponentType> listComponent(){
		Connection con = null; 
		int ac_id, supertype_id, parent, kind;
		String name;
		ArrayList<AbstractComponentType> list = new ArrayList<AbstractComponentType>(); 
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_ALL_ABSTRACTCOMPONENT); 
			ResultSet resultSet = prepared.executeQuery(); 
			while (resultSet.next()) { 
				name = resultSet.getString("ac_name");
				ac_id = resultSet.getInt("ac_id"); 
				supertype_id = resultSet.getInt("supertype_id"); 
				kind = resultSet.getInt("kind_id");
				parent = resultSet.getInt("parent");
				AbstractComponentType ac = new AbstractComponentType();
				ac.setIdAc(ac_id);
				ac.setName(name);
				ac.setSupertype(new AbstractComponentType());
				ac.getSupertype().setIdAc(supertype_id);
				ac.getSupertype().setName(DBHandler.getAbstractComponentName(supertype_id));
				ac.setParent(new AbstractComponentType());
				ac.getParent().setIdAc(parent);
				ac.setKind(DBHandler.getKindName(kind));
				list.add(ac);
			}
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return null;
		} finally { closeConnnection(con); 
		} 
		return list;
	}


	/**
	 * This method test if a context paramenter has a value, i.e. if it has kind 3, 4 or 5. If it is true, the return statement will be true, and will return false otherwise.
	 */
	public static boolean hasValue(int id){//corrigir para parametro de contexto
		Connection con = null; 
		int kind_id = -1;
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_COMPONENT_KIND); 
			prepared.setInt(1, id); 
			ResultSet resultSet = prepared.executeQuery(); 
			if(resultSet.next()) { 
				kind_id = resultSet.getInt("kind_id"); 
			}
		} catch (SQLException e) {
			e.printStackTrace(); 
			return false;
		} finally { 
			closeConnnection(con); 
		} 
		if(kind_id == 3 || kind_id == 4 || kind_id == 5){
			return true;
		}

		return false;
	}

	/**
	 * This method returns the context argument value, given the context contract id and context parameter id
	 */
	public static Object getContextArgumentValue(int cc_id, int cp_id){
		Connection con = null; 
		String value = null;
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_CONTEXT_ARGUMENT_VALUE); 
			prepared.setInt(1, cp_id);
			prepared.setInt(2, cc_id);
			ResultSet resultSet = prepared.executeQuery(); 
			if(resultSet.next()) { 
				value = resultSet.getString("ca_value"); 
			}
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return null;
		} finally { 
			closeConnnection(con); 
		} 		
		return value;
	}

	/**
	 * This method returns component kind
	 */

	public static Integer getComponentKind(int abstractComponentID){
		Connection con = null; 
		Integer value = null;
		try { 
			con = getConnection(); 
			PreparedStatement prepared = con.prepareStatement(SELECT_COMPONENT_KIND); 
			prepared.setInt(1, abstractComponentID);
			ResultSet resultSet = prepared.executeQuery(); 
			if(resultSet.next()) { 
				value = resultSet.getInt("kind_id"); 
			}
		} catch (SQLException e) { 
			e.printStackTrace(); 
			return null;
		} finally { 
			closeConnnection(con); 
		} 		
		return value;


	}



	/**
	 * This method gets a database connection
	 * @return Connection with database
	 * @throws SQLException
	 */

	public static Connection getConnection() throws SQLException { 
		Connection connection = null;   
		try {
			if(!Boolean.parseBoolean(PropertiesHandler.getProperty("core.database.testing"))){
				try {

					Context envCtx = (Context) new InitialContext().lookup("java:comp/env");
					DataSource ds = (DataSource) envCtx.lookup("jdbc/Core");
					connection = ds.getConnection();
				}
				catch (Exception e) {
					System.out.println("Connection error: " + e.getMessage());   
				}
			}else{
				try {
					try {
						connection = DriverManager.getConnection("jdbc:postgresql://"+PropertiesHandler.getProperty("core.database.address")+":"+PropertiesHandler.getProperty("core.databaase.port")+"/Core?", PropertiesHandler.getProperty("core.database.user"), PropertiesHandler.getProperty("core.database.password"));
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				} 
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connection; 
	} 

	/**
	 * this method close the database connection
	 * @param con Database connection
	 */
	public static void closeConnnection(Connection con) { 
		try { 
			con.close();
		} catch (SQLException e){ 
			e.printStackTrace(); } 
	}
}